package com.example.score.utils;

import com.example.score.entity.vo.ExamInfoExcelVo;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.stream.Stream;

/**
 * 校本总览excel导出工具类
 */
public class SchoolBasedExcelUtils {


    /* 小工具：创建单元格并塞值 */
    private static void createCell(Row row, int col, String value, CellStyle style) {
        Cell cell = row.createCell(col);
        cell.setCellValue(value);
        if (style != null) cell.setCellStyle(style);
    }

    /* ============= 工具方法 ============= */
    private static void createCell(Row row, int col, Object value, CellStyle style) {
        Cell cell = row.createCell(col);
        if (value == null) return;
        if (value instanceof Number) {
            cell.setCellValue(((Number) value).doubleValue());
        } else {
            cell.setCellValue(value.toString());
        }
        if (style != null) cell.setCellStyle(style);
    }

    /* ---------- 1. 表头样式（灰色填充 + 粗体 + 全边框 + 居中） ---------- */
    private static CellStyle createHeaderStyle(SXSSFWorkbook wb) {
        CellStyle style = wb.createCellStyle();
        // 粗体
        Font font = wb.createFont();
        font.setBold(true);
        style.setFont(font);
        // 水平垂直居中
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }

    /* ---------- 2. 数据行样式（仅边框 + 居中） ---------- */
    private static CellStyle createDataStyle(SXSSFWorkbook wb) {
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        return style;
    }

    public static void exportExcel(HttpServletResponse response, List<ExamInfoExcelVo> list) throws IOException {
        // 1. 文件名
        String fileName = URLEncoder.encode("班级作业成绩统计", "UTF-8").replaceAll("\\+", "%20");
        response.setContentType(
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + "_" + System.currentTimeMillis() + ".xlsx");

        SXSSFWorkbook wb = new SXSSFWorkbook();
        SXSSFSheet sheet = wb.createSheet("new sheet");


        CellRangeAddress className = new CellRangeAddress(0, 1, 0, 0);
        CellRangeAddress studentCount = new CellRangeAddress(0, 1, 1, 1);
        CellRangeAddress examCount = new CellRangeAddress(0, 1, 2, 2);
        CellRangeAddress submitCount = new CellRangeAddress(0, 1, 3, 3);

        CellRangeAddress excellent = new CellRangeAddress(0, 0, 4, 5);
        CellRangeAddress good = new CellRangeAddress(0, 0, 6, 7);
        CellRangeAddress pass = new CellRangeAddress(0, 0, 8, 9);
        CellRangeAddress low = new CellRangeAddress(0, 0, 10, 11);

        Stream.of(className, studentCount, examCount, submitCount, excellent, good, pass, low)
                .forEach(sheet::addMergedRegion);

        CellStyle headerStyle = createHeaderStyle(wb);


        /* 2. 创建两行（EasyPOI 会自动创建缺失行，这里手动创建） */
        Row row0 = sheet.createRow(0);
        Row row1 = sheet.createRow(1);

        /* 4. 写第一行（合并后的主表头） */
        createCell(row0, 0, "班级", headerStyle);
        createCell(row0, 1, "人数", headerStyle);
        createCell(row0, 2, "作业次数", headerStyle);
        createCell(row0, 3, "提交人数", headerStyle);
        createCell(row0, 4, "优秀", headerStyle);   // 左上角的格子
        createCell(row0, 6, "良好", headerStyle);
        createCell(row0, 8, "及格", headerStyle);
        createCell(row0, 10, "低分", headerStyle);

        /* 5. 写第二行（子表头） */
        createCell(row1, 4, "人次", headerStyle);
        createCell(row1, 5, "占比", headerStyle);
        createCell(row1, 6, "人次", headerStyle);
        createCell(row1, 7, "占比", headerStyle);
        createCell(row1, 8, "人次", headerStyle);
        createCell(row1, 9, "占比", headerStyle);
        createCell(row1, 10, "人次", headerStyle);
        createCell(row1, 11, "占比", headerStyle);

        /* 5. 写数据（从第三行开始） */
        int rowIndex = 2;

        CellStyle dataStyle = createDataStyle(wb);
        for (ExamInfoExcelVo vo : list) {
            Row row = sheet.createRow(rowIndex++);
            int col = 0;
            createCell(row, col++, vo.getClassName(), dataStyle);
            createCell(row, col++, vo.getStudentCount(), dataStyle);
            createCell(row, col++, vo.getExamCount(), dataStyle);
            createCell(row, col++, vo.getSubmitCount(), dataStyle);
            createCell(row, col++, vo.getExcellentCount(), dataStyle);
            createCell(row, col++, vo.getExcellentRate(), dataStyle);
            createCell(row, col++, vo.getGoodCount(), dataStyle);
            createCell(row, col++, vo.getGoodRate(), dataStyle);
            createCell(row, col++, vo.getPassCount(), dataStyle);
            createCell(row, col++, vo.getPassRate(), dataStyle);
            createCell(row, col++, vo.getLowCount(), dataStyle);
            createCell(row, col, vo.getLowRate(), dataStyle);
        }
        // 6. 输出
        wb.write(response.getOutputStream());
        wb.dispose();   // 清理临时文件
        wb.close();
    }
}
